<?php

//información de la conexión a la base de datos
//No olvidar que los codigos deben estar sin ""
$link = mysql_connect("localhost", "root", "ornitorrinco666") or die(mysql_error());
mysql_select_db("ipdr", $link) or die(mysql_error());
mysql_query("SET NAMES utf8");
if ($gestor = fopen("ferney1.csv", 'r')) {
    while (($datos = fgetcsv($gestor, 1000, ",", "\n")) !== FALSE) {

        $codigo = trim($datos[0]);
        $cedula = trim($datos[9]);
        $primer_apellido = $datos[7];
        $segundo_apellido = $datos[8];
        $nombre = $datos[5] . "" . $datos[6];
        ;





        $sql = "SELECT a.id  as idbene,b.city_id as idCiudad, numero_identificacion,c.id AS idpr FROM  beneficiaries a LEFT JOIN properties b On a.property_id=b.id LEFT JOIN proyects c on c.id=b.proyect_id WHERE c.codigo='$codigo' AND numero_identificacion='$cedula' ";
        $res = mysql_query($sql, $link) or die(mysql_error());
        ;
        $bene_id = 0;
        while ($row = mysql_fetch_array($res)) {
            //  echo "ENTRO" . $row['idbene'] . "<br>";

            $bene_id = $row['idbene'];
        }


        if ($bene_id == 0) {

            echo"NO EXISTE $cedula $codigo <br>";

            $sql1 = "SELECT a.id  as idbene,numero_identificacion ,b.nombre,b.id AS proId,c.name as ciudad , c.id AS city_id ,d.name AS departamento,py.codigo as codigo,py.id AS idp FROM  beneficiaries a LEFT JOIN properties b On a.property_id=b.id LEFT JOIN cities c ON c.id=b.city_id LEFT JOIN departaments d on d.id=c.departament_id LEFT JOIN proyects py on py.id=b.proyect_id WHERE  numero_identificacion='$cedula' ";
            $res1 = mysql_query($sql1, $link) or die(mysql_error());
            ;

            while ($row1 = mysql_fetch_array($res1)) {
                echo " $cedula EXISTE EN EL PREDIO  " . $row1['nombre'] . " " . $row1['ciudad'] . " " . $row1['departamento'] . " Codigo: " . $row1['codigo'] . "-" . $row1['idp'] . "<br>";

                //mysql_query("Update properties set proyect_id=(SELECT id FROM proyects WHERE codigo='$codigo') WHERE id=".$row1['proId'], $link) or die(mysql_error());
                $resc = mysql_query("SElECT  b.id as ciudad_id ,a.id AS pr_id FROM proyects a left join cities b ON a.city_id=b.id WHERE codigo='$codigo'", $link) or die(mysql_error());
                while ($row2 = mysql_fetch_array($resc)) {
                    if ($row1['city_id'] == $row2['ciudad_id']) {
                        mysql_query("Update properties set proyect_id=" . $row2['pr_id'] . " WHERE id=" . $row1['proId'], $link) or die(mysql_error());
                    }
                }
            }

            $sql3 = "SELECT a.id  as idbene,b.city_id as idCiudad, numero_identificacion,c.id AS idpr FROM  beneficiaries a LEFT JOIN properties b On a.property_id=b.id LEFT JOIN proyects c on c.id=b.proyect_id WHERE c.codigo='$codigo' AND numero_identificacion='$cedula' ";
            $res3 = mysql_query($sql3, $link) or die(mysql_error());
            $final_bene = 0;
            while ($row3 = mysql_fetch_array($res3)) {
                $final_bene = $row3['idbene'];
            }
            if ($final_bene == 0) {
                $sql4 = "Select b.id AS predio_id from proyects a LEFT JOIN properties b on a.id=b.proyect_id WHERE a.codigo='$codigo' LIMIT 0,1";
                echo $sql4 . "<br>";
                $res4 = mysql_query($sql4, $link) or die(mysql_error());
                $idp = 0;
                while ($row4 = mysql_fetch_array($res4)) {
                    $idp = $row4['predio_id'];
                }
                if ($idp != 0) {
                    if (mysql_query("INSERT INTO beneficiaries (numero_identificacion, nombres, primer_apellido, segundo_apellido,property_id) VALUES ('$cedula','$nombre','$primer_apellido','$segundo_apellido','" . $idp . "')", $link) or die(mysql_error())) {
                        echo "CREADO EL BENEFICIARIO ('$cedula','$nombre','$primer_apellido','$segundo_apellido'," . $idp . ") <br>";
                    }
                }
            }

            $sqlfin = "UPDATE FROM  beneficiaries a LEFT JOIN properties b On a.property_id=b.id LEFT JOIN proyects c on c.id=b.proyect_id SET a.calificacion_visita='No cumple' WHERE c.codigo='$codigo' AND numero_identificacion='$cedula' <br> ";
        } else {
            //echo "Si existe $codigo / $cedula<br>";
        }
    }
} else {
    echo "Error al abrir el archivo cambioDeCodigo2.csv";
}


if ($gestor = fopen("ferney1.csv", 'r')) {
    while (($datos = fgetcsv($gestor, 1000, ",", "\n")) !== FALSE) {

        $codigo = trim($datos[0]);
        $cedula = trim($datos[9]);
        $primer_apellido = $datos[7];
        $segundo_apellido = $datos[8];
        $nombre = $datos[5] . "" . $datos[6];
        ;

       
        $sql = "Update beneficiaries a LEFT JOIN properties b On a.property_id=b.id LEFT JOIN proyects c on c.id=b.proyect_id SET a.calificacion_visita='Cumple' WHERE c.codigo='$codigo' AND a.numero_identificacion='$cedula' ";
       if(mysql_query($sql, $link) or die(mysql_error())){
           
       }else{
           echo "No se actualizo $codigo - $cedula";
       }
        ;
        

    }
} else {
    echo "Error al abrir el archivo cambioDeCodigo2.csv";
}


//mysql_close($link);
?>
